begin tran
declare
@comp_code nvarchar(25) ,
@loca_code nvarchar(25) ,
@on_date datetime,
@price_grp nchar(10)

select @comp_code = 'KOYO', @loca_code = 'KO', @price_grp = 'CHN GROUP', @on_date = '2011.02.28'

set @on_date = dateadd(day, 1, @on_date)

create table #Movedtl (loca_code nvarchar(10), seq_no int, sku nvarchar(50), 
	trx_date datetime,trx_type nvarchar(20),note_no nvarchar(20),
	in_qty int, in_amt numeric(16,2),out_qty int ,out_amt numeric(16,2) )

----------------------
--  Movement Record
----------------------
---stockin----
insert into #Movedtl
select to_loca, 1, b.sku, a.trx_date, 'I', a.note_no,
	sum(b.qty),sum(b.qty*isnull(d.unit_cost,0)),0,0
from fg_stockin_hdr a 
	join fg_stockin_dtl b on a.note_no=b.note_no
	join pos_sku c on b.sku=c.sku 
	left join v_fg_org_cost d on b.note_no=d.org_in_note and b.seq_no=d.org_in_seq and b.sku=d.sku
where  
	a.status='C' and a.comp_code = @comp_code and a.trx_date >= @on_date and 
	(@loca_code='%' or to_loca = @loca_code)
group by to_loca, a.trx_date,a.note_no,b.sku 
having sum(b.qty)<>0


--- Transfer In ---
insert into #Movedtl
select a.to_loca, 2, b.sku, a.trx_date, 'T', '['+convert(nvarchar(10), a.trx_id)+']', 
	sum(b.qty),sum(b.qty*isnull(d.unit_cost,0)) , 0, 0
from 
	fg_trx_hdr a
	join ms_loca l on loca_code = a.to_loca 
	join fg_trx_constr b on a.trx_id = b.trx_id
	join pos_sku c on b.sku=c.sku
	left join v_fg_org_cost d on b.org_in_note=d.org_in_note and b.org_in_seq=d.org_in_seq and b.sku=d.sku
where 
	l.comp_code = @comp_code and a.trx_date >= @on_date and a.trx_type = 'OT' and a.status = 'C' and
	(@loca_code='%' or a.to_loca = @loca_code)
group by a.to_loca, b.sku, a.trx_date, a.trx_id
having sum(b.qty)<>0

--- Transfer Out ---
insert into #Movedtl
select a.fr_loca, 2, b.sku, a.trx_date, 'C', '['+convert(nvarchar(10), a.trx_id)+']', 
	sum(b.qty),sum(b.qty*isnull(d.unit_cost,0)) , 0, 0
from 
	fg_trx_hdr a
	join ms_loca l on loca_code = a.fr_loca 
	join fg_trx_constr b on a.trx_id = b.trx_id
	join pos_sku c on b.sku=c.sku
	left join v_fg_org_cost d on b.org_in_note=d.org_in_note and b.org_in_seq=d.org_in_seq and b.sku=d.sku
where 
	l.comp_code = @comp_code and a.trx_date >= @on_date and a.trx_type = 'TN' and a.status = 'C' and
	(@loca_code='%' or a.fr_loca = @loca_code)
group by a.fr_loca, b.sku, a.trx_date, a.trx_id
having sum(b.qty)<>0

----stockout----
insert into #Movedtl
select e.fr_loca, 3 ,b.sku , a.iss_date,'O', e.pack_no,0,0,sum(b.qty),sum(b.qty* isnull(d.unit_cost,0)) 
from fg_ship_hdr a 
	join fg_ship_constr b on a.pack_id=b.pack_id 
	join pos_sku c on b.sku=c.sku  
	left join v_fg_org_cost d on b.org_in_note=d.org_in_note and b.org_in_seq=d.org_in_seq and b.sku=d.sku
	join(
		select distinct d.comp_code, d.pack_id, d.pack_no, fr_loca 
		from sh_packfrm_dtl a 
		join sh_packfrm_hdr b on a.form_no = b.form_no 
		join fg_trx_hdr c on b.trx_id = c.trx_id 
		join sh_pack_hdr d on a.pack_id = d.pack_id
		where (@loca_code='%' or fr_loca = @loca_code) ) e on e.pack_id=a.pack_id 

where 
	a.status='C' and e.comp_code = @comp_code and a.iss_date >= @on_date 
group by e.fr_loca, a.iss_date,e.pack_no,b.sku 
having sum(b.qty)<>0

----stockout----
insert into #Movedtl
select a.loca_code, 3, b.sku, a.dlv_date,'O',a.fg_pick_no,0,0,sum(b.qty),sum(b.qty*isnull(d.unit_cost,0)) 
from 
	fg_pick_hdr a
	join fg_pick_pick_no p on a.fg_pick_no = p.fg_pick_no
	join dc_pick_constr b on p.dc_pick_no = b.pick_no
	join pos_sku c on b.sku=c.sku
	left join v_fg_org_cost d on b.org_in_note=d.org_in_note and b.org_in_seq=d.org_in_seq and b.sku=d.sku
where 
	a.status='I' and a.comp_code = @comp_code and a.dlv_date >= @on_date and
	(@loca_code='%' or a.loca_code = @loca_code)
group by a.loca_code, a.dlv_date,a.fg_pick_no,b.sku 
having sum(b.qty)<>0

-- return-in ---
insert into #Movedtl
select a.to_loca, 4, b.sku, a.dlvry_date, 'X', a.note_no, sum(b.rec_qty),sum(b.rec_qty*isnull(d.unit_cost,0)),0,0 
from 
	pos_return_hdr a
	join pos_return_constr b on a.note_no = b.note_no
	join pos_sku c on b.sku=c.sku
	join ms_loca l on l.loca_code = a.to_loca
	left join v_fg_org_cost d on b.org_in_note=d.org_in_note and b.org_in_seq=d.org_in_seq and b.sku=d.sku
where 
	a.status='C' and l.comp_code = @comp_code  and a.dlvry_date >= @on_date and b.rec_qty>0 and
	(@loca_code='%' or a.to_loca = @loca_code)
group by a.to_loca, a.dlvry_date,a.note_no,b.sku 

-- return-out ---
insert into #Movedtl
select a.ret_loca, 5, b.sku, a.trx_date, 'R', a.note_no, 0,0, sum(b.qty),sum(b.qty* isnull(d.unit_cost,0))
from 
	fg_return_hdr a
	join fg_return_constr b on a.note_no = b.note_no
	join pos_sku c on b.sku=c.sku
	left join v_fg_org_cost d on b.org_in_note=d.org_in_note and b.org_in_seq=d.org_in_seq and b.sku=d.sku
where 
	a.comp_code = @comp_code  and a.trx_date >= @on_date and
	(@loca_code='%' or a.ret_loca = @loca_code)
group by a.ret_loca, a.trx_date,a.note_no,b.sku 

-- Stock take ---
insert into #Movedtl
select a.loca_code, 5, b.sku, a.take_date, 'A', a.note_no, 
	case when sum(adj_qty)>0 then sum(adj_qty) else 0 end,
	case when sum(adj_qty)>0 then sum(adj_qty* isnull(d.unit_cost,0)) else 0 end,
	case when sum(adj_qty)<0 then -sum(adj_qty) else 0 end,
	case when sum(adj_qty)<0 then -sum(adj_qty*isnull(d.unit_cost,0)) else 0 end
from 
	fg_stk_note a
	join fg_stk_constr b on a.note_no = b.note_no
	join pos_sku c on b.sku=c.sku
	left join v_fg_org_cost d on b.org_in_note=d.org_in_note and b.org_in_seq=d.org_in_seq and b.sku=d.sku
where 
	a.comp_code = @comp_code  and a.take_date >= @on_date and
	(@loca_code='%' or a.loca_code = @loca_code)
group by a.loca_code, a.take_date,a.note_no,b.sku 


select k.style_no, p.mark_price, sum(cc.qty) as qty from 
(
select isnull(a.sku, b.sku) sku, isnull(a.onhand_qty,0) - isnull(b.mov_qty,0) qty
 from (
select c.sku, sum(c.qty) onhand_qty from fg_st_ctrl c, ms_loca l
 where c.loca_code = l.loca_code and l.comp_code = @comp_code and (@loca_code = '%' or l.loca_code = @loca_code)
 group by c.sku
) as a full join
(select sku, sum(in_qty - out_qty) mov_qty from #Movedtl group by sku) as b on a.sku = b.sku
) as cc
  join pos_sku k on cc.sku = k.sku
  left join ms_style_price p on k.style_no = p.style_no and p.price_grp = @price_grp and p.sku = ''
 group by k.style_no, p.mark_price
 order by 1, 2
rollback tran
